// ActionScript fileimport flash.data.SQLConnection;
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.errors.SQLError;
import flash.events.Event;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.events.TimerEvent;
import flash.filesystem.File;
import flash.utils.Timer;

import mx.collections.ArrayCollection;
import mx.controls.Alert;
import mx.utils.ObjectUtil;

import org.osmf.events.TimeEvent;

private var sqlc:SQLConnection = new SQLConnection();
private var sqls:SQLStatement = new SQLStatement();

[Bindable]
public var dp:ArrayCollection = new ArrayCollection();

public function db_start():void
{
	
	var db:File = File.applicationStorageDirectory.resolvePath("magazines.db");
	sqlc.openAsync(db);
	
	sqlc.addEventListener(SQLEvent.OPEN, db_opened);
	sqlc.addEventListener(SQLErrorEvent.ERROR, error);
	
	sqls.addEventListener(SQLErrorEvent.ERROR, error);
	sqls.addEventListener(SQLEvent.RESULT, result);
	
}

public function db_opened(e:SQLEvent):void
{
	sqls.sqlConnection = sqlc;
	sqls.text = "CREATE TABLE IF NOT EXISTS magazines_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, publisher_name TEXT, magazine_name TEXT, category TEXT);";
	sqls.execute();
	refresh();
}

public function addItem(publisher_name:String, magazine_name:String, category:String):void
{
	sqls.text = "INSERT INTO magazines_table (publisher_name, magazine_name, category) VALUES('" + publisher_name + "','" + magazine_name + "','" + category + "');";
	sqls.execute();
	
	refresh();
} 

private function refresh(e:TimerEvent = null):void
{
	var timer:Timer = new Timer(10,1);
	timer.addEventListener(TimerEvent.TIMER, refresh);
	
	if ( !sqls.executing )
	{
		sqls.text = "SELECT publisher_name, magazine_name, category FROM magazines_table"
		sqls.execute();
	}
	else
	{
		timer.start();
	}
}

private function result(e:SQLEvent):void
{
	var data:Array = sqls.getResult().data;
	dp = new ArrayCollection(data);
}

public function remove(id:Number):void
{
	sqls.text = "DELETE FROM magazines_table WHERE id = " + id.toString();
	sqls.execute();
	refresh();
}

public static function error(e:SQLErrorEvent):void
{
	Alert.show(e.toString());
}